﻿using System;
using System.Collections.Generic;
using System.Security;
using System.Text;

namespace SBToolkit.Utilities.Excel
{
    /// <summary>
    /// Exports IEnumerable collection to Excel.
    /// </summary>
    public static class ExcelExporter
    {
        /// <summary>
        /// Excel document template.
        /// </summary>
        private const string Document =
            "<?xml version=\"1.0\"?>" +
            "<?mso-application progid=\"Excel.Sheet\"?>" +
            "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"" +
            "          xmlns:o=\"urn:schemas-microsoft-com:office:office\"" +
            "          xmlns:x=\"urn:schemas-microsoft-com:office:excel\"" +
            "          xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"" +
            "          xmlns:html=\"http://www.w3.org/TR/REC-html40\">" +
            " <Styles>" +
            "  <Style ss:ID=\"HeaderColumn\">" +
            "   <Font ss:Bold=\"1\"/>" +
            "   <Interior ss:Color=\"#D8D8D8\" ss:Pattern=\"Solid\"/>" +
            "  </Style>" +
            "  <Style ss:ID=\"StringLiteral\">" +
            "   <NumberFormat ss:Format=\"@\"/>" +
            "  </Style>" +
            "  <Style ss:ID=\"Decimal\">" +
            "   <NumberFormat ss:Format=\"0.00\"/>" +
            "  </Style>" +
            "  <Style ss:ID=\"Integer\">" +
            "   <NumberFormat ss:Format=\"0\"/>" +
            "  </Style>" +
            "  <Style ss:ID=\"Date\">" +
            "   <NumberFormat ss:Format=\"Short Date\"/>" +
            "  </Style>" +
            " </Styles>" +
            " {0}" +
            "</Workbook>";

        /// <summary>
        /// Excel worksheet template.
        /// </summary>
        private const string Worksheet =
            "<Worksheet ss:Name=\"{0}\">" +
            " <Table>" +
            "  {1}" +
            " </Table>" +
            " <AutoFilter x:Range=\"R1C1:R1C{2}\" xmlns=\"urn:schemas-microsoft-com:office:excel\"></AutoFilter>" +
            "</Worksheet>";

        /// <summary>
        /// Exports collection of objects to Excel.
        /// </summary>
        /// <typeparam name="T">Type of objects to export.</typeparam>
        /// <param name="items">Item to export.</param>
        /// <returns>XML representing Excel file.</returns>
        public static string ToExcel<T>(this IEnumerable<T> items)
        {
            return items.ToExcel(null);
        }

        /// <summary>
        /// Exports collection of objects to Excel.
        /// </summary>
        /// <typeparam name="T">Type of objects to export.</typeparam>
        /// <param name="items">Item to export.</param>
        /// <param name="headers">Excel headers.</param>
        /// <returns>XML representing Excel file.</returns>
        public static string ToExcel<T>(this IEnumerable<T> items, params HeaderCell[] headers)
        {
            var props = typeof(T).GetProperties();
            if (headers == null)
            {
                headers = new HeaderCell[props.Length];
                for (int i = 0; i < props.Length; i++)
                {
                    headers[i] = new HeaderCell(props[i].Name);
                }
            }

            if (headers.Length != props.Length)
            {
                throw new Exception("Header must be specified foreach item property.");
            }

            return String.Format(Document, GetExcelWorksheet("Sheet", items, headers));
        }

        /// <summary>
        /// Gets Excel worksheet.
        /// </summary>
        /// <typeparam name="T">Type of objects to export.</typeparam>
        /// <param name="worksheetName">Name of the worksheet.</param>
        /// <param name="items">Items to export.</param>
        /// <param name="headers">Excel headers.</param>
        /// <returns>XML representing Excel worksheet.</returns>
        private static string GetExcelWorksheet<T>(string worksheetName, IEnumerable<T> items, IEnumerable<HeaderCell> headers)
        {
            var sb = new StringBuilder();
            sb.Append(GetExcelHeader(headers));
            foreach (var item in items)
            {
                sb.Append(GetExcelRow(item));
            }

            return String.Format(Worksheet, worksheetName, sb, typeof(T).GetProperties().Length);
        }

        /// <summary>
        /// Gets Excel headers.
        /// </summary>
        /// <param name="headers">Excel headers.</param>
        /// <returns>XML representing Excel headers.</returns>
        private static string GetExcelHeader(IEnumerable<HeaderCell> headers)
        {
            var sb = new StringBuilder();
            foreach (var header in headers)
            {
                sb.Append(GetExcelColumn(header));
            }

            sb.Append("<Row>");
            foreach (var header in headers)
            {
                sb.AppendFormat("{0}", GetExcelHeaderCell(header.Text));
            }

            sb.Append("</Row>");

            return sb.ToString();
        }

        /// <summary>
        /// Gets Excel row.
        /// </summary>
        /// <param name="item">Item to export.</param>
        /// <returns>XML representing Excel row.</returns>
        private static string GetExcelRow(object item)
        {
            var props = item.GetType().GetProperties();
            var sb = new StringBuilder("<Row>");
            foreach (var prop in props)
            {
                sb.AppendFormat("{0}", GetExcelRowCell(prop.GetValue(item, null)));
            }

            sb.Append("</Row>");

            return sb.ToString();
        }

        /// <summary>
        /// Gets Excel column.
        /// </summary>
        /// <param name="header">Excel header.</param>
        /// <returns>XML representing Excel column.</returns>
        private static string GetExcelColumn(HeaderCell header)
        {
            return String.Format("<Column ss:Width=\"{0}\"/>", header.Width.ToExcelDecimalNumber());
        }

        /// <summary>
        /// Gets Excel header cell.
        /// </summary>
        /// <param name="text">Header text.</param>
        /// <returns>XML representing Excel header cell.</returns>
        private static string GetExcelHeaderCell(string text)
        {
            return String.Format("<Cell ss:StyleID=\"HeaderColumn\"><Data ss:Type=\"String\">{0}</Data></Cell>", text);
        }

        /// <summary>
        /// Gets Excel row cell.
        /// </summary>
        /// <param name="value">Cell value.</param>
        /// <returns>XML representing Excel cell.</returns>
        private static string GetExcelRowCell(object value)
        {
            if (value == null)
            {
                value = String.Empty;
            }

            switch (value.GetType().FullName)
            {
                case "System.String":
                    return "<Cell ss:StyleID=\"StringLiteral\"><Data ss:Type=\"String\">" + value.EscapeForXml() + "</Data></Cell>";
                case "System.DateTime":
                    return "<Cell ss:StyleID=\"Date\"><Data ss:Type=\"DateTime\">" + value.ToExcelShortDate() + "</Data></Cell>";
                case "System.Boolean":
                    return "<Cell ss:StyleID=\"StringLiteral\"><Data ss:Type=\"String\">" + value.EscapeForXml() + "</Data></Cell>";
                case "System.Byte":
                case "System.Int16":
                case "System.Int32":
                case "System.Int64":
                    return "<Cell ss:StyleID=\"Integer\"><Data ss:Type=\"Number\">" + value + "</Data></Cell>";
                case "System.Single":
                case "System.Double":
                case "System.Decimal":
                    return "<Cell ss:StyleID=\"Decimal\"><Data ss:Type=\"Number\">" + value.ToExcelDecimalNumber() + "</Data></Cell>";
                case "System.DBNull":
                    return "<Cell ss:StyleID=\"StringLiteral\"><Data ss:Type=\"String\"></Data></Cell>";
                default:
                    throw new Exception(value.GetType().FullName + " type is not supported.");
            }
        }

        /// <summary>
        /// Converts decimal number to Excel format.
        /// </summary>
        /// <param name="number">Decimal number to convert.</param>
        /// <returns>Excel formated decimal number.</returns>
        private static string ToExcelDecimalNumber(this object number)
        {
            return number.ToString().Replace(',', '.');
        }

        /// <summary>
        /// Converts short date to Excel format.
        /// </summary>
        /// <param name="date">Short date to convert.</param>
        /// <returns>Excel formated short date.</returns>
        private static string ToExcelShortDate(this object date)
        {
            return date.GetType() == typeof(DateTime) ? ((DateTime)date).ToString("s") : String.Empty;
        }

        /// <summary>
        /// Escapes specified value for XML.
        /// </summary>
        /// <param name="value">The value to escape.</param>
        /// <returns>XML escaped value.</returns>
        private static string EscapeForXml(this object value)
        {
            return SecurityElement.Escape(value.ToString());
        }
    }

    /// <summary>
    /// Represents Excel header row.
    /// </summary>
    public class HeaderCell
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="HeaderCell"/> class.
        /// </summary>
        /// <param name="text">Header text.</param>
        public HeaderCell(string text)
            : this(text, 75)
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="HeaderCell"/> class.
        /// </summary>
        /// <param name="text">Header text.</param>
        /// <param name="width">Header width.</param>
        public HeaderCell(string text, double width)
        {
            Text = text;
            Width = width;
        }

        /// <summary>
        /// Gets or sets header text.
        /// </summary>
        /// <value>Header text.</value>
        public string Text
        {
            get; 
            set;
        }

        /// <summary>
        /// Gets or sets header width.
        /// </summary>
        /// <value>Header width.</value>
        public double Width
        {
            get; 
            set;
        }

        /// <summary>
        /// Performs an implicit conversion from <see cref="string"/> to <see cref="HeaderCell"/>.
        /// </summary>
        /// <param name="text">Header text.</param>
        /// <returns>The result of the conversion.</returns>
        public static implicit operator HeaderCell(string text)
        {
            return new HeaderCell(text);
        }
    }
}
